"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : PACKAGE
Case Name   : 创建package,系统管理员对普通用户赋予package的权限后用户对package
              进行操作
Create At   : 2022/04/26
Owner       : @zou_jialiang050
Description :
    1.创建用户
    2.声明包规格
    3.定义package body
    4.调用package中存储过程,查询表数据
    5.将package的所有权限以及create any package权限赋给新建用户
    6.新建用户身份进入数据库,切换至public模式重新声明定义原本package
    7.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.调用package成功,返回执行结果
    5.成功
    6.合理报错
    7.成功
History     :
"""

import os
import unittest
from yat.test import macro
from testcase.utils.Constant import Constant
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Logger import Logger


class PACKAGE(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.Con = Constant()
        self.user = 'u_package_0039'
        self.package = 'p_test_0039'
        self.procedure = 'p_package_0039_01'
        self.procedure1 = 'p_package_0039_02'
        self.table = 't_package_0039'

    def test_package(self):
        self.logger.info(f'-----{os.path.basename(__file__)} start-----')
        step = 'step1:创建用户 expect:成功'
        self.logger.info(step)
        create_user = self.primary_sh.execut_db_sql(f'''
            drop user if exists {self.user} cascade;
            create user {self.user} with password '{macro.PASSWD_REPLACE}';''')
        self.logger.info(create_user)
        self.assertIn(self.Con.CREATE_ROLE_SUCCESS_MSG, create_user,
                      '执行失败' + step)

        step = 'step2:声明package expect:成功'
        self.logger.info(step)
        spec_package = self.primary_sh.execut_db_sql(
            f'''create or replace package {self.package} is
            procedure {self.procedure}();
            end {self.package};''')
        self.logger.info(spec_package)
        self.assertIn(self.Con.create_package_success_msg, spec_package,
                      '执行失败' + step)

        step = 'step3:定义package body expect:成功'
        self.logger.info(step)
        def_package = self.primary_sh.execut_db_sql(
            f'''create or replace package body {self.package} is
            procedure {self.procedure}()
            is
            begin
               drop table if exists {self.table};
               create table {self.table}(col_text text);
               insert into {self.table} values('test');
            end;
            end {self.package};''')
        self.logger.info(def_package)
        self.assertIn(self.Con.create_package_body_success_msg, def_package,
                      '执行失败' + step)

        step = 'step4:调用package,查询表数据 expect:调用成功,返回执行结果'
        self.logger.info(step)
        invoke_package = self.primary_sh.execut_db_sql(
            f'''call {self.package}.{self.procedure}();
            select * from {self.table};''')
        self.logger.info(invoke_package)
        self.assertIn('test', invoke_package, '执行失败' + step)

        step = 'step5:将package的所有权限以及create any package权限赋给新建用' \
               '户 expect:成功'
        self.logger.info(step)
        grant_pri = self.primary_sh.execut_db_sql(f'''
            grant all privileges on package {self.package} to {self.user};
            grant create any package to {self.user};''')
        self.logger.info(grant_pri)
        self.assertEqual(grant_pri.count('GRANT'), 2, '执行失败' + step)

        step = 'step6:新建用户身份进入数据库,切换至public模式重新声明定义原本' \
               'package expect:合理报错'
        self.logger.info(step)
        spec_def_package = self.primary_sh.execut_db_sql(
            f'''set current_schema = public;
            create or replace package {self.package} is
            procedure {self.procedure1}();
            end {self.package};

            create or replace package body {self.package} is
            procedure {self.procedure1}()
            is
            begin
               drop table if exists {self.table};
               create table {self.table}(col_text text);
               insert into {self.table} values('test1');
            end;
            end {self.package};''', sql_type=f" -U {self.user} -W "
            f"'{macro.PASSWD_REPLACE}'")
        self.logger.info(spec_def_package)
        self.assertIn('SET', spec_def_package, '执行失败' + step)
        self.assertIn(f'ERROR:  must be owner of package {self.package}',
                      spec_def_package, '执行失败' + step)

    def tearDown(self):
        step = 'step7:清理环境 expect:清理环境成功'
        self.logger.info(step)
        clean_environment = self.primary_sh.execut_db_sql(
            f'''drop package {self.package};
            drop user {self.user} cascade;''')
        self.logger.info(clean_environment)
        self.assertIn(self.Con.drop_package_success_msg, clean_environment,
                      '执行失败' + step)
        self.assertIn(self.Con.DROP_ROLE_SUCCESS_MSG, clean_environment,
                      '执行失败' + step)
        self.logger.info(f'-----{os.path.basename(__file__)} end-----')
